/*
	EXEC sp_hold_report_proc 'BYMONTH'
	EXEC sp_hold_report_proc '0','0','0','0','0','0','0','0','0','0',1,'0','bymonth','XLSREPORT'

	SO_ENDCUSTOMER_ID ---> end user
	SO.SO_ODM_ID	  ----> END CUSTOMER

sriendcust -->331
srienduser -->332
select * from sales_order
*/
ALTER PROC sp_hold_report_proc

@FROMDATE	VARCHAR(50),
@TODATE		VARCHAR(50),
@CUSTOMERID	VARCHAR(200),
@PRODUCTID	VARCHAR(200),
@REGION		VARCHAR(50),
@REPOFFICE	VARCHAR(50),
@ENDUSER	VARCHAR(500),--parameter for end customer
@SALESPERSON	VARCHAR(500),
@DEFAULTPROMPT	VARCHAR(50),
@BOOKINGTYPE	VARCHAR(50),
@ORDERTYPE	INT,
@ENDCUSTOMER	VARCHAR(500),--parameter for end user
@BYPERIOD	VARCHAR(50),
@REPORTTYPE	VARCHAR(50)
/*
Sales_Hold varchar(10), Marketing_Hold varchar(10),
				Manufacturing_Hold varchar(10), Finance_Hold varchar(10),LineItem_No int,
*/
AS
BEGIN
	DECLARE @HOLD_RESULTS_TABLE table(
		[LINEITEM ID] INT,[LINE ITEM NUMBER] varchar(50),[CUSTOMER NAME] VARCHAR(65),[CUSTOMER NUMBER] VARCHAR(50), 
		[SALESORDER NUMBER] VARCHAR(50),[CUSTOMER PO] VARCHAR(50), 
		[SO DATE] DATETIME, [SO DATE WW NO] INT,[SO DATE QTR NO] INT,
		[SALES PERSON] VARCHAR(50), SALES_REP VARCHAR(50),  
		[REQUESTED DATE] DATETIME,[REQUESTED DATE WW NO] INT, [REQUESTED DATE QTR NO] INT, 
		[SCHEDULED SHIP DATE] DATETIME, [SCHEDULE DATE WW NO] INT, [SCHEDULE DATE QTR NO] INT,
		[PRODUCT NAME] VARCHAR(50),[REQUESTED QYT] INT, [UNIT PRICE] FLOAT, STATUS VARCHAR(50), 
		REGION VARCHAR(50), REPOFFICE VARCHAR (50), SALESORDERID INT,CUSTOMERID INT, PRODUCTID INT,
		[END CUSTOMER] VARCHAR(65),[END USER] VARCHAR(65),CHANGE_NUMBER INT,
		[BOOKING DATE] DATETIME,[BOOKING DATE WW NO] INT,[BOOKING DATE QTR NO] INT,
		[CHANGE DATE] DATETIME,[CHANGE DATE WW NO] INT, [CHANGE DATE QTR NO] INT,
		PERIODIC_VALUE INT, [SALES HOLD] VARCHAR(50), [MARKETING HOLD]  VARCHAR(50),[OPERATIONS HOLD] VARCHAR(50),
		[FINANCE HOLD] VARCHAR(50),QUANTITY INT,ISCONSIGNMENT BIT )

	CREATE Table #results ( lineitem_id INT,lineitem_no varchar(50),Customer_Name varchar(65),Customer_Number varchar(50), 
				Sales_Order_Number varchar(50),Customer_PO varchar(50), 
				SO_Date datetime, [SO DATE WW NO] INT,[SO DATE QTR NO] INT, 
				Sales_Person varchar(50), Sales_Rep varchar(50),  
				Req_Date datetime,[REQUESTED DATE WW NO] INT, [REQUESTED DATE QTR NO] INT,  
				Schd_Date datetime,[SCHEDULE DATE WW NO] INT, [SCHEDULE DATE QTR NO] INT, 
				Part_Number varchar(50),Req_Qyt int, Unit_Price float, Status varchar(50), 
				Region varchar(50), RepOffice varchar (50), SALESORDERID INT,
				CustomerID int, ProductID int, EndCustomer varchar(65),EndUser VARCHAR(65),CHANGE_NUMBER INT,
				BOOKING_Date datetime, [BOOKING DATE WW NO] INT,[BOOKING DATE QTR NO] INT, ISCONSIGNMENT BIT)

	DECLARE @HOLDSTRING VARCHAR(4000)

	SET @HOLDSTRING = N'
	INSERT INTO #RESULTS 
	select 
		cool.lineitem_id,cool_line_item_no, C.c_name, C.c_number, SO.salesorder_no, SO.so_customer_PO_number, 
		SO.SO_Booking_Date, So_Booking_Date_WW_No,So_Booking_Date_Qtr_No, 
		SO.so_sales_person, SO.Regional_Rep, 
		cool.cool_requested_date, Requested_Date_WW_No,Requested_Date_Qtr_No,
		cool.cool_schedule_date, Schedule_Date_WW_No,Schedule_Date_Qtr_No,
		P.part_number, cool.cool_qty, cool.cool_unit_cost, cool.COOL_Status, C.C_region, 
		SO.Regional_Rep,SO.salesorder_id, SO.Customer_ID, P.Product_ID,
		CASE WHEN ( SO_ODM_ID IS NULL OR SO_ODM_ID <= 0  ) 
			THEN C.C_Name 
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
			END AS EndUser,
		CASE WHEN ( SO_ENDCUSTOMER_ID IS NULL OR SO_ENDCUSTOMER_ID <= 0  ) 
			THEN (CASE WHEN ( SO_ODM_ID IS NULL OR SO_ODM_ID <= 0 ) 
				THEN C.C_Name 
				ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
				END) 
			ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
			END AS EndCustomer,
		( select isnull( max(ChangeNo),0 ) from customer_change_order_lineitem where lineitem_id = cool.lineitem_id) ,
		(SELECT CCO_Booking_Date from customer_change_order_lineitem where lineitem_id = cool.lineitem_id AND ChangeNo = (select isnull( max(ChangeNo),0 ) from customer_change_order_lineitem where lineitem_id = cool.lineitem_id)),
		(SELECT CCO_Booking_date_WW_no from customer_change_order_lineitem where lineitem_id = cool.lineitem_id AND ChangeNo = (select isnull( max(ChangeNo),0 ) from customer_change_order_lineitem where lineitem_id = cool.lineitem_id)),
		(SELECT CCO_Booking_Date_Qtr_No from customer_change_order_lineitem where lineitem_id = cool.lineitem_id AND ChangeNo = (select isnull( max(ChangeNo),0 ) from customer_change_order_lineitem where lineitem_id = cool.lineitem_id)),
		SO_IS_CONSIGNMENT
	from 
		customer_open_Order_lineItem cool, product P, sales_order SO, customer C
	where 
		cool.salesorder_ID = SO.salesorder_id 
		and cool.product_id = P.product_id 
		and SO.customer_id = C.customer_id 
		and ( cool.COOL_Status<> ''SHIPPED'' and  cool.COOL_Status<> ''APPROVED'' ) '

	IF @PRODUCTID IS NOT NULL AND @PRODUCTID <> '0'
	BEGIN
		SET @HOLDSTRING = @HOLDSTRING + ' AND COOL.Product_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCTID +'''))'
	END
	IF @CUSTOMERID IS NOT NULL AND @CUSTOMERID <> '0'
	BEGIN
		SET @HOLDSTRING = @HOLDSTRING + ' AND SO.Customer_ID in ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @CUSTOMERID +'''))'
	END
	IF @REGION IS NOT NULL AND @REGION <> '0'
	BEGIN
		SET @HOLDSTRING = @HOLDSTRING + ' AND C.C_REGION = '''+ @REGION+''''
	END
	IF @REPOFFICE IS NOT NULL AND @REPOFFICE <> '0'
	BEGIN
		SET @HOLDSTRING = @HOLDSTRING + ' AND SO.REGIONAL_REP = '''+ @REPOFFICE+''''
	END
	IF @ENDUSER IS NOT NULL AND @ENDUSER <> '0'
	BEGIN
		SET @HOLDSTRING = @HOLDSTRING + ' AND CASE WHEN ( SO_ODM_ID IS NULL OR SO_ODM_ID <= 0  ) 
								THEN C.C_Name 
								ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ODM_ID )
							END IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @ENDUSER +'''))'
	END
	IF @SALESPERSON IS NOT NULL AND @SALESPERSON <> '0'
	BEGIN
		SET @HOLDSTRING = @HOLDSTRING + ' AND C.C_SALES_PERSON IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @SALESPERSON +'''))'
	END
	IF @ENDCUSTOMER IS NOT NULL AND @ENDCUSTOMER <> '0'
	BEGIN
		SET @HOLDSTRING = @HOLDSTRING + ' AND CASE WHEN ( SO_ENDCUSTOMER_ID IS NULL OR SO_ENDCUSTOMER_ID <= 0  ) 
							THEN (CASE WHEN ( SO_ODM_ID IS NULL OR SO_ODM_ID <= 0 ) 
								THEN C.C_Name 
								ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID =  SO.SO_ODM_ID )
								END) 
							ELSE ( SELECT C_NAME FROM CUSTOMER WHERE CUSTOMER_ID = SO.SO_ENDCUSTOMER_ID)
							END IN ( SELECT VENDOR FROM FN_GET_STRINGS_IN_STRING ('''+ @ENDCUSTOMER +'''))'
	END

	IF @ORDERTYPE IS NOT NULL AND @ORDERTYPE <> 2
	BEGIN
		SET @HOLDSTRING = @HOLDSTRING + ' AND SO_IS_CONSIGNMENT = 1 AND SO.SO_Is_Sample = '''+ CONVERT(VARCHAR,@ORDERTYPE)+''''
	END
	ELSE IF @ORDERTYPE IS NOT NULL AND @ORDERTYPE = 2
	BEGIN
		SET @HOLDSTRING = @HOLDSTRING + ' AND SO_IS_CONSIGNMENT = 0 '
	END

	IF ( @FROMDATE IS NOT NULL AND @FROMDATE <> '0' )
	BEGIN
		SET @HOLDSTRING = @HOLDSTRING +' AND SO.SO_Booking_Date >= '''+ CONVERT(VARCHAR,@FROMDATE, 101)+ ' 12:00AM ' +''''
	END
	IF ( @TODATE IS NOT NULL AND @TODATE <> '0' )
	BEGIN
		SET @HOLDSTRING = @HOLDSTRING + +' AND SO.SO_Booking_Date <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END

	EXEC (@HOLDSTRING)

	
	INSERT  INTO @HOLD_RESULTS_TABLE
	SELECT  R.lineitem_id,R.LINEITEM_NO,Customer_Name,Customer_Number, Sales_Order_Number,Customer_PO, 
		SO_Date,[SO DATE WW NO],[SO DATE QTR NO],
		Sales_Person,Sales_Rep,
		Req_Date,[REQUESTED DATE WW NO] , [REQUESTED DATE QTR NO] , 
		Schd_Date,[SCHEDULE DATE WW NO] , [SCHEDULE DATE QTR NO] ,
		Part_Number,Req_Qyt,Unit_Price,Status,Region,RepOffice, SALESORDERID,
		CustomerID,ProductID ,EndCustomer ,EndUser ,CHANGE_NUMBER ,
		BOOKING_Date,[BOOKING DATE WW NO] ,[BOOKING DATE QTR NO] ,
		(CASE 
			WHEN  CHANGE_NUMBER = 0 THEN NULL
			ELSE  BOOKING_Date
		END ),
		(CASE 
			WHEN  CHANGE_NUMBER = 0 THEN NULL
			ELSE  [BOOKING DATE WW NO]
		END ),
		(CASE 
			WHEN  CHANGE_NUMBER = 0 THEN NULL
			ELSE  [BOOKING DATE QTR NO]
		END ),
		(CASE 
			WHEN  @BYPERIOD  = 'BYMONTH' THEN DATEPART( m, BOOKING_Date )
			WHEN  @BYPERIOD  = 'byQuarter' THEN [BOOKING DATE QTR NO]
			ELSE [BOOKING DATE WW NO]
		END),
		(select (CASE	WHEN hold_reason_code IS NOT NULL AND hold_reason_code = '0' THEN 'X' 
				ELSE hold_reason_code 
			END) from sales_hold_table where hold_by_group = 'SALES' and lineitem_id = R.lineitem_id AND SALESORDER_ID = SALESORDERID ),
		(select (CASE
				WHEN hold_reason_code IS NOT NULL AND hold_reason_code = '0' THEN 'X' 
				ELSE hold_reason_code 
			END) from sales_hold_table where hold_by_group = 'MARKETING' and lineitem_id = R.lineitem_id AND SALESORDER_ID = SALESORDERID),
		(select (CASE
				WHEN hold_reason_code IS NOT NULL AND hold_reason_code = '0' THEN 'X' 
				ELSE hold_reason_code 
			END) from sales_hold_table where hold_by_group = 'OPERATIONS' and lineitem_id = R.lineitem_id AND SALESORDER_ID = SALESORDERID),
		(select (CASE
				WHEN hold_reason_code IS NOT NULL AND hold_reason_code = '0' THEN 'X' 
				ELSE hold_reason_code 
			END) from sales_hold_table where hold_by_group = 'FINANCE' and lineitem_id = R.lineitem_id AND SALESORDER_ID = SALESORDERID),
		(CASE WHEN CSLIT_ID  > 0 THEN ( Req_Qyt - CSLIT_QTY_SHIPPED)
			ELSE Req_Qyt
		END),ISCONSIGNMENT
	FROM #RESULTS R LEFT OUTER JOIN  Customer_Shipping_Line_Item_Table CSLIT ON
	        R.LineItem_ID = CSLIT.LineItem_ID

	IF @REPORTTYPE = 'XLREPORT'
	BEGIN 
		SELECT 
			[CUSTOMER NAME],[END CUSTOMER],[END USER] ,[SALESORDER NUMBER],[LINE ITEM NUMBER],[CUSTOMER PO], 
			[SO DATE] , [SO DATE WW NO],[SO DATE QTR NO],
			[CHANGE DATE] ,[CHANGE DATE WW NO] , [CHANGE DATE QTR NO] ,
			[SALES PERSON],[SALES HOLD] , [MARKETING HOLD] ,[OPERATIONS HOLD] ,
			[FINANCE HOLD],[REQUESTED DATE],[REQUESTED DATE WW NO] , [REQUESTED DATE QTR NO] , 
			[SCHEDULED SHIP DATE], [SCHEDULE DATE WW NO] , [SCHEDULE DATE QTR NO] ,
			[PRODUCT NAME] ,QUANTITY,[UNIT PRICE],( QUANTITY*[UNIT PRICE]) AS [EXTENDED PRICE]
			
		FROM 
			@HOLD_RESULTS_TABLE ORDER BY DATEPART(yyyy,[BOOKING DATE]),PERIODIC_VALUE,[BOOKING DATE] 
	END
	ELSE
	BEGIN
		SELECT 
			[LINEITEM ID],[LINE ITEM NUMBER],[CUSTOMER NAME],[CUSTOMER NUMBER],[SALESORDER NUMBER] ,[CUSTOMER PO] , 
			[SO DATE] , [SO DATE WW NO],[SO DATE QTR NO],
			[SALES PERSON] , SALES_REP ,  
			[REQUESTED DATE],[REQUESTED DATE WW NO] , [REQUESTED DATE QTR NO] , 
			[SCHEDULED SHIP DATE], [SCHEDULE DATE WW NO] , [SCHEDULE DATE QTR NO] ,
			[PRODUCT NAME] ,[REQUESTED QYT] , [UNIT PRICE] , STATUS, 
			REGION , REPOFFICE , SALESORDERID ,CUSTOMERID , PRODUCTID ,
			[END USER] ,[END CUSTOMER] ,CHANGE_NUMBER ,
			[BOOKING DATE] ,[BOOKING DATE WW NO] ,[BOOKING DATE QTR NO] ,
			[CHANGE DATE] ,[CHANGE DATE WW NO] , [CHANGE DATE QTR NO] ,
			PERIODIC_VALUE , [SALES HOLD] , [MARKETING HOLD] ,[OPERATIONS HOLD] ,
			[FINANCE HOLD] ,QUANTITY,( QUANTITY*[UNIT PRICE]) AS [EXTENDED PRICE],
			DATEPART(yyyy,[BOOKING DATE]) AS [ACTUAL YEAR],ISCONSIGNMENT
		FROM 
			@HOLD_RESULTS_TABLE ORDER BY DATEPART(yyyy,[BOOKING DATE]),PERIODIC_VALUE,[BOOKING DATE] 
	END
END
